The Hierarchical FlexGrid Control

The Hierarchical FlexGrid control is another grid control included in Visual Basic 6. Unlike the DataGrid control, the Hierarchical FlexGrid control can merge contiguous cells in different rows if they contain the same values. This control really shines when you assign a hierarchical ADO Recordset to its DataSource property, because it can correctly display multiple bands—where each band is a set of data columns—that come from a different child Recordset in the hierarchical data structure, as shown in Figure 15-5. The only serious limitation of this control is that it's read-only—the cells can't be directly edited by the user.

The easiest way to create a Hierarchical FlexGrid control is to build a hierarchical Command object in a DataEnvironment designer, use the right mouse button to drop it on a form, and select the Hierarchical Flex Grid command from the pop-up menu. This operation adds the necessary references to the control's type library and links the newly created Hierarchical FlexGrid control to the Command object. All the examples in this section—as well as in the demonstration program on the CD—are based on the hierarchical Recordset obtained by setting a relationship among the Authors, Title, and Titles tables of the Biblio.mdb database.

The Hierarchical FlexGrid control is included in the MSHFLXGD.OCX file, which must therefore be distributed with any application that uses this control.

Figure 15-5. A Hierarchical FlexGrid control showing a three-level hierarchical ADO Recordset (the Authors, Title, and Titles tables in Biblio.mdb).

After you've created a bound Hierarchical FlexGrid control, you can right-click on it and select the Retrieve Structure menu command, which fills the grid with column headers, each one referring to a different field in the data source. Unfortunately, this grid doesn't expose an Edit command, so you can't use the mouse to modify the column layout and widths at design time. Unlike the DataGrid control, the Hierarchical FlexGrid doesn't expose an object model.

The General tab, shown in Figure 15-6, allows you to assign a value to the control's Rows and Cols properties, which—as you probably expect—determine the number of rows and columns in the grid. These properties, however, affect the appearance of the control only in unbound mode—that is, when its DataSource doesn't point to an ADO data source. In all other cases, the dimensions of the grid depend on the number of records and fields in the source. The FixedRows and FixedCols properties affect how many fixed (nonscrollable) rows and columns are displayed at the left and on the upper borders of the grid. If the AllowBigSelection property is True, clicking on a row or column header selects the entire row or column.

The Highlight property affects the appearance of selected cells and can be one of the following enumerated values: 0-flexHighlightNever, 1-flexHighlightAlways (the default—selected cells are always highlighted), and 2-flexHighlightWithFocus (selected cells are highlighted only when the control has the focus). The FocusRect property determines which kind of border appears around the current cell: 0-flexFocusNone (no border), 1-flexFocusLight (the default), or 2-flexFocusHeavy.

The BandDisplay property can change how bands are displayed in the control and can be either 0-flexBandDisplayHorizontal (the default, all bands corresponding to a record are displayed on the same row) or 1-flexBandDisplayVertical (each band is displayed in a separate row). Under normal circumstances, setting the grid's Text property or another cell-formatting property affects only the current cell; you can change this default behavior by changing the value of the FillStyle property from 0-flexFillSingle to 1-flexFillRepeat, in which case all the selected cells will be affected by the assignment. The SelectionMode property decides whether you can select any cell (0-flexSelectionFree, the default) or are forced to select entire rows (1flexSelectionByRow) or entire columns (2-flexSelectionByColumn).

The AllowUserResizing property determines whether the user can resize rows or columns with the mouse and takes one of the following values: 0-flexResizeNone (no resizing is allowed), 1-flexResizeColumns, 2-flexResizeRows, or 3-flexResizeBoth (the default). If this property is set to 2-flexResizeRows or 3-flexResizeBoth, you can limit the effect of a row resize with the RowSizingMode property, which can be 0flexRowSizeIndividual (only the resized row is affected—this is the default) or 1flexRowSizeAll (all rows are resized).

Figure 15-6. The General tab of the Property Pages dialog box of a Hierarchical FlexGrid control.

The Bands tab is probably the most important tab in the Property Pages dialog box of the Hierarchical FlexGrid control, because here you decide which fields in the parent and child Recordsets are visible in the grid. Typically, you should hide those numeric key fields that are meaningless to the user and repeated occurrences of foreign keys. For example, in the demonstration program I hid the Au_ID and ISBN fields in Band 1 (the band that refers to the intermediate Title Author table), because the Au_ID is meaningless to the user and the ISBN field is already displayed in Band 2 (the band that refers to the Titles table). Because all the fields in Band 1 are invisible, the grid actually displays only two bands. You can also change the column caption of any visible field, as shown in Figure 15-7.

The Bands tab also allows you to set a few other Band attributes. In the GridLines field, you can select the type of line to draw between the current band and the next one. This value corresponds to the GridLinesBand property and can be one of the following values: 0-flexGridNone, 1-flexGridFlat (the default—the color is determined by the GridColor property), 2-flexGridInset, 3-flexGridRaised, 4-flexGridDashes, or 5-flexGridDots.

In the TextStyle combo box, you can select the 3-D effect used for displaying text in the band. This corresponds to the TextStyleBand property and can be one of the following values: 0-flexTextFlat (the default), 1-flexTextRaised, 2-flexTextInset, 3-flexTextRaisedLight, or 4-flexTextInsetLight. Settings 1 and 2 work best for larger bold fonts, while settings 3 and 4 work better for smaller fonts. The TextStyleHeader property can take the same values but affects the style of the text in column headers.

The BandIndent property sets the number of columns by which a band is indented; this property has an effect only when the BandDisplay property is set to 1flexBandDisplayVertical. The BandExpandable Boolean property specifies whether the band can be expanded or collapsed; a plus or minus symbol is displayed in the first column of the band, unless the band is the last one on its row. The last property on this tab, ColumnHeaders, determines whether the grid displays column headers above the band.

Figure 15-7. The Bands tab lets you decide which fields are visible in each band and what their captions are.

The Style tab allows you to set a few other properties that affect the appearance of the grid. The GridLinesFixed property corresponds to the style of the grid lines (allowed values are the same as for the GridLinesBand property). The TextStyleFixed property determines the 3-D style used for text in the fixed rows and columns (using the same values as for the TextStyleBand property).

The MergeCells property determines how adjacent cells with similar values can be merged; this property is used only when the grid is manually filled with values and has no effect when the control is bound to a hierarchical ADO Recordset. (See the Visual Basic online documentation for further information.)

The RowHeightMin property is the minimum height for rows, in twips. The GridLinesUnpopulated property affects the style of cells that don't contain any values. The WordWrap property should be set to True if you want the text in cells to wrap if longer than the cell's width.

The Hierarchical FlexGrid control exposes many color and font properties that you can assign in the Color and Font tabs, respectively. See the Visual Basic documentation for additional details on these properties.

The Hierarchical FlexGrid control has nearly 160 properties, and their thorough description would require more pages than we can afford here. But since most of these properties affect minor details of the control's appearance, they aren't tremendously interesting. What you really need is a guided tour through the most important properties, methods, and events.

The most significant run-time properties of the Hierarchical FlexGrid control are Row, Col, and Text, which set and return the coordinates and contents of the current cell. Remember that this control is inherently read-only: You can programmatically change the contents of any cell in the grid, but the new value won't be stored in the database. Also keep in mind that the grid automatically merges cells with the same values. For example, in the grid displayed in Figure 15-4, the cells in column 1 and rows 2 through 5 have the same value, and you can change this value by setting the Text property for any one of the cells that were merged together.

The control exposes a number of read-only properties that return information about the current cell. For example, you can find out what band the current cell belongs to by querying the BandLevel property, and you can determine the type of the current cell by querying the grid's CellType property, which returns one of the following values: 0-flexCellTypeStandard, 1-flexCellTypeFixed, 2-flexCellTypeHeader, 3-flexCellTypeIndent, or 4-flexCellTypeUnpopulated.

Unlike the DataGrid control, the Hierarchical FlexGrid control allows you to completely determine the appearance of the current cell, through properties such as CellBackColor, CellForeColor, CellFontName, CellFontSize, CellFontBold, CellFontItalic, CellFontUnderline, CellFontStrikeThrough, and CellFontWidth. For example, the following code lets the user highlight any cell by double-clicking on it to change its background to red:

Private Sub MSHFlexGrid1_DblClick() If MSHFlexGrid1.CellBackColor = vbWindowBackground Then ' Highlight a cell with white text on red background. MSHFlexGrid1.CellBackColor = vbRed MSHFlexGrid1.CellForeColor = vbWhite Else ' Restore default colors. MSHFlexGrid1.CellBackColor = vbWindowBackground MSHFlexGrid1.CellForeColor = vbWindowText End If End Sub

The CellTextStyle property determines the 3-D aspect of the text in the current cell. The CellAlignment property sets and returns the alignment attribute for the text in the currently selected cells; it can have one of the following values: 0flexAlignLeftTop, 1-flexAlignLeftCenter, 2-flexAlignLeftBottom, 3flexAlignCenterTop, 4flexAlignCenterCenter, 5-flexAlignCenterBottom, 6flexAlignRightTop, 7flexAlignRightCenter, 8flexAlignRightBottom, 9-flexAlignGeneral (the default setting—strings to the left, numbers to the right).

You can also display an image in the current cell by assigning a suitable value to the CellPicture property and specifying the image's alignment through the CellPictureAlignment property. For example, you can display a string of text in the upper left corner and a picture in the lower right corner:

MSHFlexGrid1.CellAlignment = flexAlignLeftTop MSHFlexGrid1.Text = "This is an arrow" MSHFlexGrid1.CellPictureAlignment = flexAlignRightBottom ' You might need to edit the path to this icon file. Set MSHFlexGrid1.CellPicture = LoadPicture( _ "C:\Microsoft Visual Studio\Graphics\Icons\Arrows\Arw02rt.ico")

If the FillStyle property has been set to 1-flexFillRepeat, most of the properties already mentioned will affect all the cells in the selected range. These include the CellPicture, CellPictureAlignment, and all the CellFontxxxx properties. You therefore have a way to change the formatting of a group of cells by assigning the same properties you would assign for a single cell. A word of caution: Although you can assign a value to the Text property to fill all the selected cells with the same string, I found that on some occasions this action raises the error "Method 'Text' of 'IMSHFlexGrid' failed." For this reason, you shouldn't assign a value to the Text property when more than one cell is selected, or you should at least protect such an assignment with an On Error statement.

To make the best use of the capacity to affect multiple cells with the assignment to a single property, you must learn how to use the RowSel and ColSel properties to retrieve the coordinates of the current selected range. These properties return the row and column of the cell at one corner of the rectangular selection area. The cell at the opposite corner is always the active cell and is therefore pointed to by the Row and Col properties. This means that to iterate on all the cells in the current selection you must write code such as the following:

' Evaluate the sum of all the cells in the current selection. Dim total As Double, r As Long, c As Long Dim rowMin As Long, rowMax As Long Dim colMin As Long, colMax As Long ' Determine the minimum and maximum row and column. If MSHFlexGrid1.Row < MSHFlexGrid1.RowSel Then rowMin = MSHFlexGrid1.Row rowMax = MSHFlexGrid1.RowSel Else rowMin = MSHFlexGrid1.RowSel rowMax = MSHFlexGrid1.Row End If If MSHFlexGrid1.Col < MSHFlexGrid1.ColSel Then colMin = MSHFlexGrid1.Col colMax = MSHFlexGrid1.ColSel Else colMin = MSHFlexGrid1.ColSel colMax = MSHFlexGrid1.Col End If ' Loop on all the selected cells. On Error Resume Next For r = rowMin To rowMax For c = colMin To colMax total = total + CDbl(MSHFlexGrid1.TextMatrix(r, c)) Next Next

This code uses the TextMatrix property, which returns the contents of any cell in the grid. The code works correctly even if a cell spans multiple rows or columns, because in this case TextMatrix returns a nonempty value only for the row/column combination corresponding to the top left corner of the merged cell, so you never count the same number more than once.

The Clip property offers an efficient way to assign the contents of the cells that are currently selected. First, prepare a tab-delimited string, where individual rows are separated by vbCr characters and individual columns by vbTab characters. Then adjust the RowSel and ColSel properties to select a range of cells, and finally assign the string to the Clip property:

Dim clipString As String clipString = "TopLeft" & vbTab & "TopRight" & vbCr & "BottomLeft" _ & vbTab & "BottomRight" & vbCr ' Range must be 2 rows by 2 columns to match the clipString. MSHFlexGrid1.RowSel = MSHFlexGrid1.Row + 1 MSHFlexGrid1.RowCol = MSHFlexGrid1.Col + 1 MSHFlexGrid1.Clip = clipString

According to the documentation, this property should also return the contents of the current range as a tab-delimited string; unfortunately, there must be a bug somewhere because this property always returns an empty string. The Clip property works correctly in an MSFlexGrid control, so watch out when porting older Visual Basic 5 programs to Visual Basic 6. Until this bug is fixed, you can simulate the Clip property by using the following routine:

' Return the Clip property for an MSHFlexGrid control. Function MSHFlexGrid_Clip(FlexGrid As MSHFlexGrid) As String Dim r As Long, c As Long, result As String Dim rowMin As Long, rowMax As Long Dim colMin As Long, colMax As Long ' Find minimum and maximum row and column in selected range. If FlexGrid.Row < FlexGrid.RowSel Then rowMin = FlexGrid.Row rowMax = FlexGrid.RowSel Else rowMin = FlexGrid.RowSel rowMax = FlexGrid.Row End If If FlexGrid.Col < FlexGrid.ColSel Then colMin = FlexGrid.Col colMax = FlexGrid.ColSel Else colMin = FlexGrid.ColSel colMax = FlexGrid.Col End If ' Build the clip string. For r = rowMin To rowMax For c = colMin To colMax result = result & FlexGrid.TextMatrix(r, c) If c <> colMax Then result = result & vbTab Next result = result & vbCr Next MSHFlexGrid_Clip = result End Function

The Clip property is also useful to work around a known problem in the Hierarchical FlexGrid control: the control can't display more the 2048 rows when used in bound mode. When you bind the grid to a data source with more than 2048 records, the Rows property contains the correct number of records, but only the first 2048 records are displayed in the grid. To display all the records in the data source, you can use the ADO Recordset's GetString property to retrieve all the records and assign its result to the grid's Clip property. For additional information, see article Q194653 in the Microsoft Knowledge Base.

You can choose among several properties that affect the attributes of a column. The ColAlignment property affects how all the values in standard cells in a column are displayed:

' Align the contents of all standard cells in column 2 to center and bottom. ' Column indexes are zero-based. MSHFlexGrid1.ColAlignment(2) = flexAlignCenterBottom

The ColAlignmentFixed property does the same, but it affects the cells in the fixed rows:

' Align column headers to left and center. MSHFlexGrid1.ColAlignmentFixed(2) = flexAlignLeftCenter

The ColWordWrapOption property can be set to True to enable word wrapping in all the standard cells in a column, whereas the ColWordWrapOptionFixed property affects the wrapping status of the column header cells:

' Enable word wrapping in all cells in column 5. MSHFlexGrid1.ColWordWrapOption(4) = True MSHFlexGrid1.ColWordWrapOptionFixed(4) = True

The Hierarchical FlexGrid control offers a nonstandard way to set column and row headers. You can set them individually, using the TextMatrix property, but you can assign them in a single operation by using the FormatString property. In this case, you have to pass the column headers separated by pipe characters (|). You can precede column headers with special characters that affect their alignment (< for left, ^ for center, and > for right); you can also add a section to the format string, separated by a semicolon, containing the headers for all the strings. Here's an example:

' Display year numbers in column headers and month names in row headers. MSHFlexGrid1.FormatString = "Sales|> 1998|> 1999|> 2000" _ & ";Sales|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec"

The width of each column caption indirectly affects the width of the column itself. For a more precise setting, use the ColWidth property. A bug in the implementation of this property makes the Hierarchical FlexGrid control ignore the formatting characters when used with fixed columns. Everything works well with a regular FlexGrid control. (For additional information, see article Q197362 in the Microsoft Knowledge Base.)

While the Hierarchical FlexGrid control is inherently a read-only control, it doesn't take much effort to add some basic editing capabilities to it. The trick, as you might have guessed, is to superimpose a TextBox control over the current cell so that it appears as if it belongs to the grid. You need to trap a few events to keep the TextBox in sync with the grid, but on the whole it doesn't require much code.

To make this technique work, add a TextBox control to the form and then set its Visible property to False, its MultiLine property to True, and its BorderStyle property to 0-None. These routines cause the phantom TextBox (named txtCellEditor) to appear and disappear as needed:

' These variables keep track of the cell that was active ' when edit mode was entered. Dim cellRow As Long, cellCol As Long Sub ShowCellEditor() With MSHFlexGrid1 ' Cancel range selection, if any. .RowSel = .Row .ColSel = .Col ' Move the cell editor into place by making it one pixel smaller ' than the current cell. txtCellEditor.Move .Left + .CellLeft, .Top + .CellTop, _ .CellWidth - ScaleX(1, vbPixels, vbTwips), _ .CellHeight - ScaleY(1, vbPixels, vbTwips) ' Transfer the contents of the current cell into the TextBox. txtCellEditor.Text = .Text ' Move the TextBox in front of the grid. txtCellEditor.Visible = True txtCellEditor.ZOrder txtCellEditor.SetFocus ' Remember current coordinates for later. cellRow = .Row cellCol = .Col End With End Sub Sub HideCellEditor(Optional Cancel As Boolean) ' Hide the TextBox control if necessary. If txtCellEditor.Visible Then ' If the operation hasn't been canceled, transfer the contents ' of the TextBox into the cell that was active. If Not Cancel Then MSHFlexGrid1.TextMatrix(cellRow, cellCol) = txtCellEditor.Text End If txtCellEditor.Visible = False End If End Sub

The ShowCellEditor routine can move the TextBox into place, thanks to the grid's CellLeft, CellTop, CellWidth, and CellHeight properties. The next step is to determine when cell editing is activated. In the demonstration program, this happens when the grid is double-clicked or when the user presses an alphanumeric key when the grid has the input focus:

Private Sub MSHFlexGrid1_DblClick() ShowCellEditor End Sub Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer) ShowCellEditor ' If it's an alphanumeric key, it is passed to the TextBox. If KeyAscii >= 32 Then txtCellEditor.Text = Chr$(KeyAscii) txtCellEditor.SelStart = 1 End If End Sub

Edit mode is terminated when the TextBox loses the focus (for example, when the user clicks elsewhere in the grid), or when either the Enter or Esc key is pressed:

Private Sub txtCellEditor_LostFocus() HideCellEditor End Sub Private Sub txtCellEditor_KeyPress(KeyAscii As Integer) Select Case KeyAscii Case 13 HideCellEditor Case 27 HideCellEditor True ' Also cancel the edit. End Select End Sub

It should be noted that this simple example modifies only the contents of the Hierarchical FlexGrid control without affecting the underlying ADO hierarchical Recordset. Updating the underlying ADO hierarchical Recordset is a more complex task, but the grid offers all the properties you need to determine which field in which record should be modified.